*Set the root to the directory: 
global root "/Volumes/PortableSSD/Electricvehicles/ReplicationPackage" 


***
*From the created excel datasets, now we can create the tables necessary for the paper: 
***

*Save the tables in the folder tables_graphs02: 
cd "$root/Results/tables_graphs/main"

*Generate the table of the initial probabilities:

frame create init_prob
frame change init_prob
import excel "$root/Results/estimation_results/counterfactuals/init_prob.xlsx", sheet("Sheet1") firstrow clear
 
frame create init_prob_inc
frame change init_prob_inc
import excel "$root/Results/estimation_results/counterfactuals/init_prob_byinc.xlsx", sheet("Sheet1") firstrow clear
 
frame put drivetype wealth_group prob, into(inc_prob)
frame change inc_prob

reshape wide prob, i(drivetype) j(wealth_group)

frame change init_prob
frame put drivetype prob, into(tab4)

frame change tab4
frlink 1:1 drivetype, frame(inc_prob)
frget prob*, from(inc_prob)
drop inc_prob

set obs 5
replace drivetype=0 if _n==5
gen pred="pred"

order pred, after(drivetype)
sort drivetype

frame create init_ms
frame change init_ms
use "$root/Data/Produced/EV_welfare04.dta", clear

keep id drivetype wealth_group choice
frame put _all, into(init_ms_inc)

gcollapse (sum) choice (nunique) id, by(drivetype)

gen prob=choice/id 

frame change init_ms_inc

gcollapse (sum) choice (nunique) id, by(drivetype wealth_group)

gen prob=choice/id 

keep drivetype prob wealth_group
reshape wide prob, i(drivetype) j(wealth_group)

frame change init_ms
frlink 1:1 drivetype, frame(init_ms_inc)
frget prob*, from(init_ms_inc)

keep drivetype prob*
set obs 5 
replace drivetype=0 if _n==5
gen pred="obs"

order pred, after(drivetype)
sort drivetype

frame change tab4 
frameappend init_ms

mkmat prob prob1 prob2 prob3 prob4, matrix(Tab4) 

matrix rownames Tab4 = panA gas diesel elec hy panB gas2 diesel2 elec2 hy2
foreach num of numlist 1(1)10 {
foreach numb of numlist 1(1)5 {
matrix Tab4[`num',`numb']=round(Tab4[`num',`numb'],.0001)*100
}
}

estadd matrix Tab4, replace
#delim ;
estout matrix(Tab4, fmt(%9.0gc)) using pred_prob.tex, 
style(tex)  mlabels(,none) cells(e(Tab4)(fmt(%9.4f)))
varlabels ( panA "Panel A: Predicted"
			gas "Gasoline"
			diesel "Diesel"
			elec "Electric"
			hy "Hybrid"
			panA "Panel B: Observed"
			gas2 "Gasoline"
			diesel2 "Diesel"
			elec2 "Electric"
			hy2 "Hybrid"
             )
collabels ( "Overall" 
			"\nth{1} quartile" 
			 "\nth{2} quartile"  
			 "\nth{3} quartile" 
			 "\nth{4} quartile")
prehead("\begin{tabular}{@{}lccccc@{}} \toprule")  
posthead("\midrule")
prefoot("\bottomrule") 
postfoot("\end{tabular}") replace;
#delim cr


**
*Generate the table of changes in probabilities (tax counterfactual): 
*

frame create tax_prob
frame change tax_prob
import excel "$root/Results/estimation_results/counterfactuals/alt3_prob.xlsx", sheet("Sheet1") firstrow clear

frlink 1:1 drivetype, frame(init_prob)

foreach var of varlist co2_ann emb_co2_ann petrol_tax tax_total tax_pv veh_tax {

rename `var' `var'_alt
}

frget _all, from(init_prob)

gen wealth_group=0

 
frame create tax_prob_inc
frame change tax_prob_inc
import excel "$rootResults/estimation_results/counterfactuals/alt3_prob_byinc.xlsx", sheet("Sheet1") firstrow clear

foreach var of varlist co2_ann emb_co2_ann petrol_tax tax_total tax_pv veh_tax {

rename `var' `var'_alt
}

frlink 1:1 drivetype wealth_group, frame(init_prob_inc)

frget _all, from(init_prob_inc)

frame change tax_prob
frameappend tax_prob_inc

drop init_prob

gen d_prob=prob_count3-prob
gen d_prob_perc=d_prob/prob


frame put d_prob wealth_group drivetype, into(tab5) 

frame change tab5

reshape wide d_prob, i(drivetype) j(wealth_group)


mkmat d_prob0 d_prob1 d_prob2 d_prob3 d_prob4, matrix(Tab5) 

matrix rownames Tab5 =  gas diesel elec hy
foreach num of numlist 1(1)4 {
foreach numb of numlist 1(1)5 {
matrix Tab5[`num',`numb']=round(Tab5[`num',`numb'],.000001)*100
}
}

estadd matrix Tab5, replace
#delim ;
estout matrix(Tab5, fmt(%9.0gc)) using change_prob_vehtax.tex, 
style(tex)  mlabels(,none) cells(e(Tab5)(fmt(%9.4f)))
varlabels ( gas "Gasoline"
			diesel "Diesel"
			elec "Electric"
			hy "Hybrid"
             )
collabels ( "Overall" 
			"\nth{1} quartile" 
			 "\nth{2} quartile"  
			 "\nth{3} quartile" 
			 "\nth{4} quartile")
prehead("\begin{tabular}{@{}lccccc@{}} \toprule")  
posthead("\midrule")
prefoot("\bottomrule") 
postfoot("\end{tabular}") replace;
#delim cr



*Generate the table change in welfare (levy counterfactual): 
frame create tax_CS
frame change tax_CS
import excel "$root/Results/estimation_results/counterfactuals/CS3.xlsx", sheet("Sheet1") firstrow clear

frame change tax_prob

foreach var in co2_ann emb_co2_ann tax_total tax_pv petrol_tax veh_tax {

gen d_`var'=`var'_alt-`var'

}


frame put d_co2_ann d_emb_co2_ann d_tax_total d_tax_pv d_petrol_tax d_veh_tax wealth_group drivetype tax_pv_alt petrol_tax_alt veh_tax_alt, into(Tab6)

frame change Tab6

collapse (sum) d_co2_ann d_emb_co2_ann d_tax_total d_tax_pv d_petrol_tax d_veh_tax tax_pv_alt petrol_tax_alt veh_tax_alt , by(wealth_group)

replace d_co2_ann=d_co2_ann/1000
replace d_emb_co2_ann=d_emb_co2_ann/1000
gen d_co2_tot=d_co2_ann+d_emb_co2_ann

frlink 1:1 wealth_group, frame(tax_CS)
frget _all, from(tax_CS)

drop CS_init be_init CS_perc_change be_perc_change


egen tot_CS_change=total(CS_change)
egen tot_be_change=total(be_change)


replace CS_change=tot_CS_change if wealth_group==0
replace be_change=tot_be_change if wealth_group==0

*Generate experienced CS change: 
gen CSexp_change=CS_change-be_change

local r=0.06
local cap=1/`r' - (1/(`r'*(1+`r')^15))
di `cap'

gen d_wel=(CS_change*1000-(be_change*1000)+d_veh_tax+d_tax_pv+(abs(d_co2_tot)*175+d_petrol_tax)*`cap')/1000

gen d_pub=(d_veh_tax+d_tax_pv+(d_petrol_tax*`cap'))/1000
gen d_co2_m=abs(d_co2_tot)*`cap'*175/1000

gen TOT_EINK=59514
replace TOT_EINK=87082 if wealth_group==2
replace TOT_EINK=115592 if wealth_group==3
replace TOT_EINK=195182 if wealth_group==4

replace TOT_EINK=114341.3 if wealth_group==0

gen d_wel_inc=100*d_wel/(TOT_EINK*2307.5*`cap'/1000)
replace d_wel_inc=100*d_wel/(TOT_EINK*9230*`cap'/1000) if wealth_group==0

local r=0.06
local cap=1/`r' - (1/(`r'*(1+`r')^15))

gen total_tax_pv=veh_tax_alt+tax_pv_alt+(petrol_tax_alt*`cap')

gen total_tax_pv_per_person=total_tax_pv/2307.5
replace total_tax_pv_per_person=total_tax_pv/9230 if wealth_group==0
gen tax_inc=100*total_tax_pv_per_person/(TOT_EINK*`cap')

replace d_petrol_tax=d_petrol_tax/1000
replace d_veh_tax=d_veh_tax/1000
replace d_tax_total=d_tax_total/1000

drop d_tax_pv d_co2_tot tot_CS_change tot_be_change TOT_EINK total_tax_pv total_tax_pv_per_person veh_tax_alt tax_pv_alt petrol_tax_alt d_wel_inc

*Transpose the data to create the desired table: 
gen PanA=.
gen PanB=. 
gen PanC=.
gen PanD=.

order wealth_group PanA CS_change be_change CSexp_change PanB d_tax_total d_petrol_tax d_veh_tax tax_inc d_pub PanC d_co2_ann d_emb_co2_ann d_co2_m PanD d_wel 
xpose, clear varname
order _varname, before(v1)

drop in 1

*Create the table: 
mkmat v1 v2 v3 v4 v5 , matrix(Tab6) 

matrix rownames Tab6 = panA cs_d be_d cs_d_tot panB tax_d ptax_d veh_tax_d tax_inc d_pub panC co2_d co2_d_emb co2_m panD d_wel
foreach num of numlist 1(1)17 {
foreach numb of numlist 1(1)5 {
matrix Tab6[`num',`numb']=round(Tab6[`num',`numb'],.001)
}
}

estadd matrix Tab6, replace
#delim ;
estout matrix(Tab6, fmt(%9.0gc)) using welfare_counter_tax.tex, 
style(tex)  mlabels(,none) cells(e(Tab6)(fmt(%9.4f)))
varlabels ( 
panA "\textit{Panel A: Consumer Welfare}"
cs_d "\(\Delta\) Cons. surplus (Decision) (TCHF)"
be_d "\(\Delta\) Belief error (TCHF)"
cs_d_tot "\(\Delta\) Cons. surplus (experienced) (TCHF)"
panB "\textit{Panel B: Public finance}"
ptax_d "\(\Delta\) Fuel levy (TCHF p.a)"
tax_d "\(\Delta\) Car registration taxes (TCHF p.a)"
veh_tax_d "\(\Delta\) Vehicle tariffs (TCHF)"
tax_inc "Tax incidence (\%)"
d_pub "\(\Delta\) Public revenue (TCHF)"
panC "\textit{Panel C: Emissions}"
co2_d "\(\Delta\) \(CO_2\) pipe (t p.a.)"
co2_d_emb "\(\Delta\) \(CO_2\) embodied  (t p.a.)"
co2_m "\(\Delta\) \(CO_2\) value (TCHF)"
panD "\textit{Panel D: Overall}"
d_wel "Overall Welfare effect (TCHF)"
             )
collabels ( "Overall"
			"\nth{1} quartile" 
			 "\nth{2} quartile"  
			 "\nth{3} quartile" 
			 "\nth{4} quartile"
			 )
prehead("\begin{tabular}{@{}lccccc@{}} \toprule")  
posthead("\midrule")
prefoot("\bottomrule") 
postfoot("\end{tabular}") replace;
#delim cr


**
*Generate the table of changes in probabilities (subsidy counterfactual): 
**

frame create subs_prob
frame change subs_prob
import excel "$root/Results/estimation_results/counterfactuals/alt2_prob.xlsx", sheet("Sheet1") firstrow clear

frlink 1:1 drivetype, frame(init_prob)

foreach var of varlist co2_ann emb_co2_ann petrol_tax tax_total tax_pv paid_subsidy veh_tax {

rename `var' `var'_alt
}

frget _all, from(init_prob)

gen wealth_group=0

 
frame create subs_prob_inc
frame change subs_prob_inc
import excel "$root/Results/estimation_results/counterfactuals/alt2_prob_byinc.xlsx", sheet("Sheet1") firstrow clear

foreach var of varlist co2_ann emb_co2_ann petrol_tax tax_total tax_pv paid_subsidy veh_tax {

rename `var' `var'_alt
}

frlink 1:1 drivetype wealth_group, frame(init_prob_inc)

frget _all, from(init_prob_inc)

frame change subs_prob
frameappend subs_prob_inc

drop init_prob

gen d_prob=prob_count2-prob
gen d_prob_perc=d_prob/prob


frame put d_prob wealth_group drivetype, into(tab7)

frame change tab7

reshape wide d_prob, i(drivetype) j(wealth_group)


mkmat d_prob0 d_prob1 d_prob2 d_prob3 d_prob4, matrix(Tab7) 

matrix rownames Tab7 =  gas diesel elec hy
foreach num of numlist 1(1)4 {
foreach numb of numlist 1(1)5 {
matrix Tab7[`num',`numb']=round(Tab7[`num',`numb'],.000001)*100
}
}

estadd matrix Tab7, replace
#delim ;
estout matrix(Tab7, fmt(%9.0gc)) using change_prob_subs.tex, 
style(tex)  mlabels(,none) cells(e(Tab7)(fmt(%9.4f)))
varlabels ( gas "Gasoline"
			diesel "Diesel"
			elec "Electric"
			hy "Hybrid"
             )
collabels ( "Overall" 
			"\nth{1} quartile" 
			 "\nth{2} quartile"  
			 "\nth{3} quartile" 
			 "\nth{4} quartile")
prehead("\begin{tabular}{@{}lccccc@{}} \toprule")  
posthead("\midrule")
prefoot("\bottomrule") 
postfoot("\end{tabular}") replace;
#delim cr



*Generate the table change in welfare (subsidy counterfactual): 
frame create subs_CS
frame change subs_CS
import excel "$rootResults/estimation_results/counterfactuals/CS2.xlsx", sheet("Sheet1") firstrow clear

frame change subs_prob


foreach var in co2_ann emb_co2_ann tax_total tax_pv petrol_tax veh_tax {

gen d_`var'=`var'_alt-`var'

}

gen co2_tot=co2_ann+emb_co2_ann

frame put d_co2_ann d_emb_co2_ann co2_tot paid_subsidy d_tax_total d_tax_pv d_petrol_tax d_veh_tax wealth_group drivetype tax_pv_alt petrol_tax_alt veh_tax_alt, into(Tab8)

frame change Tab8

collapse (sum) d_co2_ann d_emb_co2_ann co2_tot paid_subsidy d_tax_total d_tax_pv d_petrol_tax d_veh_tax tax_pv_alt petrol_tax_alt veh_tax_alt , by(wealth_group)

gen co2_perc=(d_co2_ann+d_emb_co2_ann)/co2_tot*100

replace d_co2_ann=d_co2_ann/1000
replace d_emb_co2_ann=d_emb_co2_ann/1000
gen d_co2_tot=d_co2_ann+d_emb_co2_ann

frlink 1:1 wealth_group, frame(subs_CS)
frget _all, from(subs_CS)

drop CS_init subs_CS CS_perc_change 


egen tot_CS_change=total(CS_change2)
egen tot_be_change=total(be_change2)


replace CS_change=tot_CS_change if wealth_group==0
replace be_change=tot_be_change if wealth_group==0

*Generate experienced CS change: 
gen CSexp_subs_change=CS_change2-be_change2

local r=0.06
local cap=1/`r' - (1/(`r'*(1+`r')^15))
di `cap'

gen d_wel=(CS_change2*1000-(be_change2*1000)-paid_subsidy+d_veh_tax+d_tax_pv+(abs(d_co2_tot)*175+d_petrol_tax)*`cap')/1000

gen d_pub=(-paid_subsidy+d_veh_tax+d_tax_pv+(d_petrol_tax*`cap'))/1000
gen d_co2_m=abs(d_co2_tot)*`cap'*175/1000

gen TOT_EINK=59514
replace TOT_EINK=87082 if wealth_group==2
replace TOT_EINK=115592 if wealth_group==3
replace TOT_EINK=195182 if wealth_group==4

replace TOT_EINK=114341.3 if wealth_group==0

gen d_wel_inc=100*d_wel/(TOT_EINK*2307.5*`cap'/1000)
replace d_wel_inc=100*d_wel/(TOT_EINK*9230*`cap'/1000) if wealth_group==0

local r=0.06
local cap=1/`r' - (1/(`r'*(1+`r')^15))

gen total_tax_pv=-paid_subsidy+veh_tax_alt+tax_pv_alt+(petrol_tax_alt*`cap')

gen total_tax_pv_per_person=total_tax_pv/2307.5
replace total_tax_pv_per_person=total_tax_pv/9230 if wealth_group==0
gen tax_inc=100*total_tax_pv_per_person/(TOT_EINK*`cap')

replace d_petrol_tax=d_petrol_tax/1000
replace d_veh_tax=d_veh_tax/1000
replace d_tax_total=d_tax_total/1000

replace paid_subsidy=paid_subsidy/1000


drop co2_tot d_tax_pv co2_perc d_co2_tot tot_CS_change tot_be_change TOT_EINK total_tax_pv total_tax_pv_per_person veh_tax_alt tax_pv_alt petrol_tax_alt d_wel_inc

*Transpose the data to create the desired table: 
gen PanA=.
gen PanB=. 
gen PanC=.
gen PanD=.

order wealth_group PanA CS_change be_change CSexp_subs_change PanB paid_subsidy d_tax_total d_petrol_tax d_veh_tax tax_inc d_pub PanC d_co2_ann d_emb_co2_ann d_co2_m PanD d_wel
xpose, clear varname
order _varname, before(v1)

drop in 1

*Create the table: 
mkmat v1 v2 v3 v4 v5 , matrix(Tab8) 

matrix rownames Tab8 = panA cs_d be_d cs_d_tot panB subs tax_d ptax_d veh_tax_d tax_inc d_pub panC co2_d co2_d_emb co2_m panD d_wel 
foreach num of numlist 1(1)17 {
foreach numb of numlist 1(1)5 {
matrix Tab8[`num',`numb']=round(Tab8[`num',`numb'],.001)
}
}

estadd matrix Tab8, replace
#delim ;
estout matrix(Tab8, fmt(%9.0gc)) using welfare_counter_subsidy.tex, 
style(tex)  mlabels(,none) cells(e(Tab8)(fmt(%9.4f)))
varlabels ( 
panA "\textit{Panel A: Consumer Welfare}"
cs_d "\(\Delta\) Cons. surplus (Decision) (TCHF)"
be_d "\(\Delta\) Belief error (TCHF)"
cs_d_tot "\(\Delta\) Cons. surplus (experienced) (TCHF)"
panB "\textit{Panel B: Public finance}"
subs "Total subsidy (TCHF)"
ptax_d "\(\Delta\) Fuel levy (TCHF p.a)"
tax_d "\(\Delta\) Car registration taxes (TCHF p.a)"
veh_tax_d "\(\Delta\) Vehicle tariffs (TCHF)"
tax_inc "Tax incidence (\%)"
d_pub "\(\Delta\) Public revenue (TCHF)"
panC "\textit{Panel C: Emissions}"
co2_d "\(\Delta\) \(CO_2\) pipe (t p.a.)"
co2_d_emb "\(\Delta\) \(CO_2\) embodied  (t p.a.)"
co2_m "\(\Delta\) \(CO_2\) value (TCHF)"
panD "\textit{Panel D: Overall}"
d_wel "Overall Welfare effect (TCHF)"
             )
collabels ( "Overall"
			"\nth{1} quartile" 
			 "\nth{2} quartile"  
			 "\nth{3} quartile" 
			 "\nth{4} quartile"
			 )
prehead("\begin{tabular}{@{}lccccc@{}} \toprule")  
posthead("\midrule")
prefoot("\bottomrule") 
postfoot("\end{tabular}") replace;
#delim cr
